package cmu.mobile.lab;
import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

public class Database {
	public static final String KEY_ID = "id";
    public static final String ITEMID = "itemid";
    public static final String FRIEND = "friend";
    public static final String OWNER = "owner";
    public static final String STORE = "store";
    public static final String PRICE = "price";
    public static final String IMAGE = "image";
    public static final String RATING = "rating";
    public static final String GREAT = "great";
    public static final String SOSO = "soso";
    public static final String NOWAY = "noway";
    //public static final String RATING = "rating";

    private static final String TAG = "database";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    //private Cursor find;
    private static MMSContent ItemInfo;

   //Create table that includes ratings from friends
    private static final String DATABASE_CREATE_SMSCONTENT =
        "create table SMSContent (id integer primary key autoincrement, " + "itemid integer, "
        + "rating text, " + "friend text not null, FOREIGN KEY(friend) REFERENCES Person(id));";
  
    //Create table of items
    private static final String CREATE_ITEMS=
         "create table Items (itemid integer primary key autoincrement, " + "store text, " + "price text,"+
         " owner text not null, image text not null, FOREIGN KEY(itemid) REFERENCES SMSContent(itemid), FOREIGN KEY(owner) REFERENCES Person(id));";
    
   //Create table of persons 
  private static final String PERSON=
      	"create table Person (id text primary key, name text not null);";

	 private static final String DATABASE_ADDPerson =
		     "INSERT into Person (id, name) VALUES ('4124556678', 'Lenisha');";
	 
	 private static final String DATABASE_ADDPerson1 =
	     "INSERT into Person (id, name) VALUES ('6072204335', 'Julie');";
	 
	 private static final String DATABASE_ADDPerson2 =
	     "INSERT into Person (id, name) VALUES ('2017886018', 'Joon');";
	       // "INSERT into Items (itemid, store, price, owner, image) VALUES (" +ItemInfo.getItemID()+ ", '"+ ItemInfo.getStore() +"'," + ItemInfo.getPrice() + ", "+ ItemInfo.getOwnerID()+ ", '" + ItemInfo.getImagePath()+ "');";
	 
	 //Dummy data for ratings, to check if getting average of ratings works
	 private static final String DATABASE_ADDSMSContent =
		 "INSERT into SMSContent (id, itemid, rating, friend) VALUES (null,1,'great','6125924953');";
	 
	 private static final String DATABASE_ADDSMSContent1=
		 "INSERT into SMSContent (id, itemid, rating, friend) VALUES (null,2,'soso','4124556678');";
	 
	 private static final String DATABASE_ADDSMSContent2=
		 "INSERT into SMSContent (id, itemid, rating, friend) VALUES (null,3,'noway','2017886018');";
	 
	 private static final String DATABASE_ADDSMSContent3=
		 "INSERT into SMSContent (id, itemid, rating, friend) VALUES (null,3,'noway','4124556678');";
		 
	 private static final String DATABASE_ADDSMSContent4=
		 "INSERT into SMSContent (id, itemid, rating, friend) VALUES (null,1,'great','4124556678');";
	 
	 private static final String DATABASE_ADDSMSContent5=
		 "INSERT into SMSContent (id, itemid, rating, friend) VALUES (null,2,'noway','4124556678');";
	 
	 private static final String DATABASE_ADDSMSContent6=
		 "INSERT into SMSContent (id, itemid, rating, friend) VALUES (null,2,'noway','2017886018');";
	 
	 private static final String DATABASE_ADDSMSContent7=
		 "INSERT into SMSContent (id, itemid, rating, friend) VALUES (null,1,'noway','2017886018');";
	 
	 private static final String DATABASE_ADDSMSContent8=
		 "INSERT into SMSContent (id, itemid, rating, friend) VALUES (null,1,'great','8888888888');";
	 
	 private static final String DATABASE_ADDSMSContent9=
		 "INSERT into SMSContent (id, itemid, rating, friend) VALUES (null,2,'noway','8888888888');";
	 
	 private static final String DATABASE_ADDSMSContent10=
		 "INSERT into SMSContent (id, itemid, rating, friend) VALUES (null,2,'noway','6125924953');";
	 
	 private static final String DATABASE_ADDSMSContent11=
		 "INSERT into SMSContent (id, itemid, rating, friend) VALUES (null,3,'noway','6125924953');";
	 
	private static final String DATABASE_ADDItem=
		"INSERT into Items (itemid, store, price, owner, image) VALUES (null,'americaneagle', 10, '4124556678','images');";
	
	private static final String DATABASE_ADDItem1=
		"INSERT into Items (itemid, store, price, owner, image) VALUES (null,'bebe', 5, '6072204335','images');";
	
	private static final String DATABASE_ADDItem2=
		"INSERT into Items (itemid, store, price, owner, image) VALUES (null,'handm', 3, '6072204335','images');";
	
	private static final String DATABASE_ADDItem3=
		"INSERT into Items (itemid, store, price, owner, image) VALUES (null,'forever21', 1, '2017886018','images');";
    
	 private static final String DATABASE_TABLE1 = "SMSContent";
	 private static final String DATABASE_TABLE2 = "Items";
    private static final String DATABASE_TABLE3 = "Person";
    private static final String DATABASE_NAME = "fashionista";
	private static final int DATABASE_VERSION = 3;

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
        	super(context, DATABASE_NAME, null, DATABASE_VERSION);

        }

        @Override
        public void onCreate(SQLiteDatabase db) {        	
        	Log.d("*n*****", "I'm here");
             String sqlString = "PRAGMA foreign_keys = ON;";
             db.execSQL(sqlString);
  
             try {
            	 db.execSQL(DATABASE_CREATE_SMSCONTENT);
            	 db.execSQL(CREATE_ITEMS);
            	 db.execSQL(PERSON);
            	 db.execSQL(DATABASE_ADDItem);
            	 db.execSQL(DATABASE_ADDItem1);
            	 db.execSQL(DATABASE_ADDItem2);
            	 db.execSQL(DATABASE_ADDItem3);
            	 db.execSQL(DATABASE_ADDSMSContent);
            	 db.execSQL(DATABASE_ADDSMSContent1);
            	 db.execSQL(DATABASE_ADDSMSContent2);
            	 db.execSQL(DATABASE_ADDSMSContent3);
  
            	 db.execSQL(DATABASE_ADDPerson);
            	 db.execSQL(DATABASE_ADDPerson1);
            	 db.execSQL(DATABASE_ADDPerson2);
            	 
            	 
             } catch (Exception e) {
            	 Log.d("EXCEPTION", e.getMessage());
             }
        }
        
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS DATABASE_TABLE3;");
            db.execSQL("DROP TABLE IF EXISTS DATABASE_TABLE1;");
            db.execSQL("DROP TABLE IF EXISTS DATABASE_TABLE2;");
            onCreate(db);
        }

    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public Database(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the fashionista database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public Database open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        try {
        	
        	mDb = mDbHelper.getWritableDatabase();
        	Log.d("*nn**", ((Boolean)mDb.isOpen()).toString());
       
        } catch (Exception e){
        	Log.d("EXCEPTIONXXXX", e.getMessage());
        }
        return this;
    }

    public void close() {
        mDbHelper.close();
    }


    //Add item
    public void addItem(MMSContent mms) {
    	mDb.execSQL("INSERT into Items (itemid, store, price, owner, image) VALUES (null, '" + mms.getStore() +"', '" + mms.getPrice() + "', '"+ mms.getOwnerID()+ "', '" + mms.getImagePath()+ "');");
    	
    }
    
    //Rate an item
    public void rateItem(int itemID, MMSContent mms) {
    	mDb.execSQL("INSERT into SMSContent (id, itemid, rating, friend) VALUES (null, '" + itemID +"', '" + mms.getRating() + "', '"+ mms.getFriend()+ "');");
    	
    }
    
    
    /////////////return content
    
    public Cursor getAllItems() 
    {
        Cursor mCursor =

            mDb.query(DATABASE_TABLE2, new String[] {ITEMID,
                     STORE, PRICE, OWNER, IMAGE},
                     null, null, null, null, null);
        
           

         return mCursor;
    }


    public ArrayList<MMSContent> getAllFriendsItems() {
    	// TODO: return mms content array... with every item with an owner id that isn't you
    	Cursor find1 = mDb.query(DATABASE_TABLE2, new String[] {ITEMID,
                      STORE, PRICE, OWNER, IMAGE},
                      "owner!='6072204335'", null, null, null, null);
    	
     	ArrayList<MMSContent> mmslist = new ArrayList<MMSContent>();

    	find1.moveToFirst();
        while (find1.isAfterLast() == false) {
        	MMSContent item= new MMSContent();        	
        	String itemid=find1.getString(0);
        	Integer id =Integer.parseInt(itemid);
        	String store =find1.getString(1);
        	String price =find1.getString(2);
        	String owner =find1.getString(3);
        	String image =find1.getString(4);
       
        	
        	item.setItemID(id);
        	item.setStore(store);
        	item.setPrice(price);
        	item.setOwnerID(owner);
        	item.setImagePath(image);
        	mmslist.add(item);
       
        	find1.moveToNext();

        }
        find1.close();
    	return mmslist;
    }

    public ArrayList<MMSContent> getRating(int itemID) {
    	
    	// TODO: return mms content array... get all the ratings of an item
    	Cursor find2 = mDb.query(DATABASE_TABLE1, new String[] {RATING, FRIEND},
                "itemid="+itemID, null, null, null, null);
	
    	ArrayList<MMSContent> mmslist = new ArrayList<MMSContent>();

    	find2.moveToFirst();
    	while (find2.isAfterLast() == false) {
    		MMSContent item= new MMSContent();   
    		
    		String rating =find2.getString(0);
    		String friend =find2.getString(1);
    
    		item.setRating(rating);
    		item.setFriend(friend);
    		mmslist.add(item);
 
    		find2.moveToNext();
    	}
    	find2.close();
	return mmslist;
    }
    
    public ArrayList<MMSContent> getAllMyItems() {
    	Cursor find3 = 
    	 mDb.query(DATABASE_TABLE2, new String[] {ITEMID,
                 STORE, PRICE, OWNER, IMAGE},
                 null, null, null, null, null);
    	
    	// TODO: return mms content array... get all of the items that with your own id
    	ArrayList<MMSContent> mmslist = new ArrayList<MMSContent>();

    	find3.moveToFirst();
        while (find3.isAfterLast() == false) {
        	MMSContent item= new MMSContent();        	
        	String itemid=find3.getString(0);
        	Integer id =Integer.parseInt(itemid);
        	String store =find3.getString(1);
        	String price =find3.getString(2);
        	String owner =find3.getString(3);
        	String image =find3.getString(4);
       
        	
        	item.setItemID(id);
        	item.setStore(store);
        	item.setPrice(price);
        	item.setOwnerID(owner);
        	item.setImagePath(image);
        	mmslist.add(item);
       
        	find3.moveToNext();

        }
        find3.close();
    	return mmslist;
    }

}
